/**
 * 添加一些公共的信息
 * eg: 学院、运动
 */
const db = require('../../config/db')
const dayjs = require('dayjs')

/**
 * 添加学院
 * @param {*} req 
 * @param {*} res 
 */
exports.addCollege = async(req, res) => {
    try {
        const {collegeName} = req.body
        const sql = `INSERT INTO college 
                (college)
                values
                (?)`
        const params = [collegeName]
        const [rows] = await db.query(sql, params)
        if (rows.affectedRows === 0) {
            return res.status(201).json({ error: "添加学院失败，请稍后重试" });
        }
        res.status(200).json({
            id: rows.insertId, // 新插入的 id
            college: collegeName,
        });
    } catch(err) {
        console.log("addCollege error: ", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}

/**
 * 删除学院
 * @param {*} req 
 * @param {*} res 
 * todo 写删除学院后，跟该学院相关联的东西，且需要root用户才能删除
 */
exports.deleteCollege = async(req, res) => {
    try {
        const {collegeId} = req.body
        const sql = `DELETE FROM college 
                     where id = ?`
        const params = [collegeId]
        const [rows] = await db.query(sql, params)
        if (rows.affectedRows === 0) {
            return res.status(201).json({ error: "未找到该学院，删除失败" });
        }
        res.status(200).json({
            message: "删除成功!"
        });
    } catch(err) {
        console.log("deleteCollege error: ", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}


/**
 * 添加运动
 * @param {*} req 
 * @param {*} res 
 */
exports.addSport = async(req, res) => {
    try {
        const {sportName} = req.body
        // 1. 先检查是否已存在
        const checkSql = `SELECT id FROM sport WHERE sport = ? LIMIT 1`;
        const [existRows] = await db.query(checkSql, [sportName]);

        if (existRows.length > 0) {
        return res.status(201).json({ error: "该运动已存在，请勿重复添加" });}
        const sql = `INSERT INTO sport 
                (sport)
                values
                (?)`
        const params = [sportName]
        const [rows] = await db.query(sql, params)
        if (rows.affectedRows === 0) {
            return res.status(201).json({ error: "添加运动失败，请稍后重试" });
        }
        res.status(200).json({
            id: rows.insertId, // 新插入的 id
            sport: sportName,
        });
    } catch(err) {
        console.log("addSport error: ", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}

/**
 * 删除运动
 * @param {*} req 
 * @param {*} res 
 * todo 写删除运动后，跟该运动相关联的东西，且需要root用户才能删除
 */
exports.deleteSport = async(req, res) => {
    try {
        const {sportId} = req.body
        const sql = `DELETE FROM sport 
                     where id = ?`
        const params = [sportId]
        const [rows] = await db.query(sql, params)
        if (rows.affectedRows === 0) {
            return res.status(201).json({ error: "未找到该运动，删除失败" });
        }
        res.status(200).json({
            message: "删除成功!"
        });
    } catch(err) {
        console.log("deleteSport error: ", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}

/**
 * 获取反馈
 */
exports.getFeedback = async(req, res) => {
    try {
        const {content} = req.body
        let sql
        let params = []
        if(content.trim() != "") {
            sql = `SELECT fb.id, us.nickname, fb.content, fb.create_time
                   FROM feedback fb
                   LEFT JOIN user us
                   ON fb.open_id = us.open_id
                   where content LIKE ?
                   ORDER BY fb.create_time DESC`
            params.push(`%${content}%`)
        } else {
            sql = `SELECT fb.id, us.nickname, fb.content, fb.create_time
                   FROM feedback fb
                   LEFT JOIN user us
                   ON fb.open_id = us.open_id
                   ORDER BY fb.create_time DESC`
        }
        const [rows] = await db.query(sql, params)
        res.status(200).json(
            rows.map(row => {
                // console.log("数据库原始时间:", row.create_time)
                return {
                    id: row.id,
                    nickname: row.nickname,
                    content: row.content,
                    createTime: dayjs(row.create_time).format('YYYY-MM-DD HH:mm:ss')
                }
            })
        )
    } catch(err) {
        console.log("error:", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}

/**
 * Delete Feedback By Id
 */
exports.deleteFeedback = async(req, res) => {
    try {
        const {feedbackId} = req.body
        console.log("feedbackId: ", feedbackId)
        const sql = `DELETE FROM feedback
                     where id = ?`
        const [rows] = await db.query(sql, [feedbackId])
        if(rows.affectedRows == 0) {
            return res.status(201).json({error: "删除反馈失败!"})
        }
        res.status(200).json({success: "删除反馈成功!"})
    } catch(err) {
        console.log("error:", err)
        res.status(500).json({error: "服务器错误，请稍后重试!"})
    }
}